﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Dynamic;
using System.Web;
using System.Web.Mvc;
using System.Data.OleDb;
using System.Data;
using VCC.Model;
using System.Collections;
namespace VCC.CMS.Controllers
{
    public class ImportDataController : Controller
    {
        //
        // GET: /ImportData/

        public ActionResult Index()
        {
            ChoVietDataContext eDataContext = new ChoVietDataContext();
            List<Suburb> LSub = new List<Suburb>();
            string filePath = Server.MapPath("\\Uploads\\AustralianPostCode.xls");
            if (!System.IO.File.Exists(filePath)) return View();
            string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0";
            OleDbConnection oledbConn = new OleDbConnection(connString);
            try
            {
                oledbConn.Open();
                OleDbCommand cmd = new OleDbCommand("Select * from [Aupost$]", oledbConn);
                OleDbDataAdapter oleda = new OleDbDataAdapter();
                oleda.SelectCommand = cmd;
                DataSet ds = new DataSet();
                oleda.Fill(ds);
                string PostCode =ds.Tables[0].Columns[1].ToString();
                int i = 0;
                while ((int.Parse(ds.Tables[0].Rows[i][0].ToString()) > 0))
                {
                    Suburb sb = new Suburb();
                    sb.PostCode = int.Parse(ds.Tables[0].Rows[i][0].ToString());
                    //Chuẩn hóa Tên
                    string temName = ds.Tables[0].Rows[i][1].ToString().Trim();
                    temName = temName.ToLower();
                    string finalName = "";
                    //int j = 0;
                    for (int j = 0; j < temName.Length; j++)
                    {
                        if (j == 0 || (temName[j - 1].ToString() == " " && temName[j].ToString() != " "))
                        {
                            finalName = finalName + temName[j].ToString().ToUpper();
                        }
                        else {
                            finalName = finalName + temName[j];
                        }
                    }
                    sb.Name = finalName;
                    
                    sb.StateName = ds.Tables[0].Rows[i][2].ToString();
                    sb.Latitude = decimal.Parse(ds.Tables[0].Rows[i][3].ToString());
                    sb.Longitude =decimal.Parse(ds.Tables[0].Rows[i][4].ToString());
                    //Tổng hợp Full Name
                    sb.FullName = finalName +", "+sb.StateName + ", "+sb.PostCode;
                    LSub.Add(sb);
                    i++;
                }
                oledbConn.Close();
                eDataContext.Suburbs.InsertAllOnSubmit(LSub);
                eDataContext.SubmitChanges();
                return View();
            }
            catch (Exception e)
            {
                return View();
            }
            
        }

    }
}
